﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using LdExpress;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    public string ContainerPage = "./TruckerSale.aspx";
    public string strCondition = "";
    public int NumOfItemsPerPage = 20;
    public string strPager = "";
    public string strIDs = "";
    public string strError = "";
    public string strMess = "";
    public int nPermissions = 0;
    public int nPage = 1;
    public string strPageID = "page_number";
    public string strLetter = "letter";
    public string strAlphaBet = "";
    public string strChar = "";
    public string strExcluded = "ID";
    public string strPartQuery = "";
    protected void Page_Load(object sender, EventArgs e)
    {

        int nPage = 1;
        try
        {

            if (Request.QueryString[strPageID] != null)
                nPage = int.Parse(Request.QueryString[strPageID]);
        }
        catch
        {
            nPage = 1;
        }
        if (nPage <= 0) nPage = 1;


        try
        {

            if (Request.QueryString[strLetter] != null)
            {
                strChar = Request.QueryString[strLetter].ToString();
                strCondition = " AND BizName like '" + strChar + "%'";
            }
        }
        catch
        {
            strChar = "";
        }

        if ((Session["logined_id"] != null) && (int.Parse(Session["logined_id"].ToString()) > 0))
        {
            if (Session["TruckerSale"] != "") nPermissions = int.Parse(Session["TruckerSale"].ToString());
        }
        else
            Response.Redirect("Login.aspx");

        if (!Page.IsPostBack)
        {
            LoadDropDownPic();
            LoadDropDownTrucker();
            if (Session["PicSalseTrucker"] != null)
                ddPicSales.SelectedValue = Session["PicSalseTrucker"].ToString();
            if (Session["BizNameTrucker"] != null)
                ddTruckers.SelectedValue = Session["BizNameTrucker"].ToString();
            if (Session["LocationTrucker"] != null)
                ddLocationSearch.SelectedValue = Session["LocationTrucker"].ToString();
            CommonFunctions.GetAllQueryString(Request, "", "action;ID", out strPartQuery);
            if (Request.QueryString["action"] == "delete" && nPermissions > 7)
            {
                TruckerSale obj = new TruckerSale();
                obj.ID = long.Parse(Request.QueryString["ID"].ToString());
                obj.Delete(GV.cn, GV.trans);
                Response.Redirect("TruckerSale.aspx" + strPartQuery);
            }
            if (Request.QueryString["ID"] != null)
                loadTruckerSale();
            LoadDropDownLocationTrucker();
            //if (nPermissions < 3)
            //ddLocationSearch.Visible = false;

            bool isDisplayTruckerSales = false;

            if (Session["PicSalseTrucker"] != null && Session["PicSalseTrucker"].ToString().ToLower() != "all")
            {
                if (Session["PicSalseTrucker"].ToString() != "")
                    strCondition += " AND Pic like'%" + DBModule.RefineString(Session["PicSalseTrucker"].ToString()) + "%'";
                else
                    strCondition += " AND Pic =''";
            }

            if (Session["BizNameTrucker"] != null && Session["BizNameTrucker"].ToString().ToLower() != "all")
            {
                if (Session["BizNameTrucker"].ToString() != "")
                {
                    strCondition += " AND BizName like '%" + DBModule.RefineString(Session["BizNameTrucker"].ToString()) + "%'";
                }
                else
                    strCondition += " AND BizName =''";
            }
            if (Session["LocationTrucker"] != null && Session["LocationTrucker"].ToString().ToLower() != "all")
            {
                if (Session["LocationTrucker"].ToString() != "")
                {
                    strCondition += " AND tbl_TruckerSale.LocationID=" + Session["LocationTrucker"].ToString();
                }
                else
                    strCondition += " AND tbl_TruckerSale.LocationID =0";
            }
            if (strCondition.Length > 0) isDisplayTruckerSales = true;
            if (nPermissions < 9)
                strCondition += " AND tbl_TruckerSale.LocationID in(" + Session["UserLocationIDs"].ToString() + ")";
            if (Request.QueryString[strLetter] != null)
            {
                isDisplayTruckerSales = true;
            }
            if (isDisplayTruckerSales)
            {
                #region Build List
                int nNumOfPages = 0;

                DataTable dtList;
                BuildList(strCondition, NumOfItemsPerPage, nPage, out nNumOfPages, out dtList, null, null);
                if (nPage > nNumOfPages) nPage = 1;

                rptData.DataSource = dtList;
                rptData.DataBind();
                #endregion
                #region Build Pager
                CommonFunctions.BuildPager(Request, nNumOfPages, nPage, strPageID, ContainerPage, out strPager);
                #endregion
            }
            CommonFunctions.BuildAphabetSearch(Request, ContainerPage, strLetter, strChar, strExcluded, out strAlphaBet);
        }

    }
    protected void loadTruckerSale()
    {
        TruckerSale obj = new TruckerSale();
        obj.ID = long.Parse(Request.QueryString["ID"]);
        obj.Load(GV.cn, GV.trans);
        txtPIC.Text = obj.Pic;
        txtAdvantage.Text = obj.Advantage;
        txtTel.Text = obj.Tel;
        txtCell.Text = obj.Cell;
        txtSkype.Text = obj.Skype;
        txtEmail.Text = obj.Email;
        txtNumberCar.Text = obj.NumberCar.ToString();
        txtRank.Text = obj.Rank;
        txtServiceArea.Text = obj.ServiceArea;
        txtNote.Text = obj.Note;
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        strError = "";
        strMess = "";
        TruckerSale obj = new TruckerSale();
        if (Request.QueryString["ID"] != null)
        {
            obj.ID = long.Parse(Request.QueryString["ID"]);
            obj.Load(GV.cn, GV.trans);
        }
        obj.TruckerID = long.Parse(ddTrucker.SelectedValue);
        obj.LocationID = long.Parse(ddLocation.SelectedValue);
        obj.Pic = txtPIC.Text.Trim();
        obj.Advantage = txtAdvantage.Text.Trim();
        obj.Tel = txtTel.Text;
        obj.Cell = txtCell.Text;
        obj.Skype = txtSkype.Text;
        obj.Email = txtEmail.Text;
        try
        {
            obj.NumberCar = int.Parse(txtNumberCar.Text);
        }
        catch { }
        obj.Rank = txtRank.Text;
        obj.ServiceArea = txtServiceArea.Text;
        obj.Note = txtNote.Text;
        if (ddTrucker.SelectedValue == "0")
        {
            lblError.Text = "Trucker is not Empty.";
            return;
        }

        obj.Save(GV.cn, GV.trans);
        CommonFunctions.GetAllQueryString(Request, "", "action;ID", out strPartQuery);
        Response.Redirect("TruckerSale.aspx" + strPartQuery);
    }
    private void BuildList(string strCondition, int NumOfItemsPerPage, int PageNo, out int nNumOfPages, out DataTable dtList, MySqlConnection cn, MySqlTransaction trans)
    {
        DataSet ds = null;
        dtList = new DataTable();
        dtList.Columns.Add("ID");
        dtList.Columns.Add("Index");
        dtList.Columns.Add("BizName");
        dtList.Columns.Add("LocationName");
        dtList.Columns.Add("Pic");
        dtList.Columns.Add("PicID");
        dtList.Columns.Add("Advantage");
        dtList.Columns.Add("Tel");
        dtList.Columns.Add("Cell");
        dtList.Columns.Add("Skype");
        dtList.Columns.Add("Email");
        dtList.Columns.Add("NumberCar");
        dtList.Columns.Add("Rank");
        dtList.Columns.Add("ServiceArea");
        dtList.Columns.Add("Note");
        dtList.Columns.Add("Action");
        //Get List by SoIDs
        nNumOfPages = 0;
        TruckerSale.GetList(strCondition, "BizName", NumOfItemsPerPage, PageNo, out nNumOfPages, out ds, GV.cn, GV.trans);

        string strAllQueryString = "";

        foreach (string strKey in Request.QueryString.AllKeys)
        {
            if (strKey != null)
            {
                if ((strKey.ToLower() != "action") && (strKey.ToLower() != "id"))
                    strAllQueryString = strAllQueryString + "&" + strKey + "=" + Request.QueryString[strKey].ToString();
            }
        }

        if (strAllQueryString.Length >= 1)
            strAllQueryString = strAllQueryString.Substring(1);

        if (ds != null)
        {
            int count = 0;
            if (Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "")
            {
                int pagenumber = 1;
                try
                {
                    pagenumber = int.Parse(Request.QueryString["page_number"]);
                    count = NumOfItemsPerPage * (pagenumber - 1);
                }
                catch { count = 0; }
            }
            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                count++;
                string ID = dr["ID"].ToString();
                string strIndex = count.ToString();
                string BizName = dr["BizName"].ToString();
                string LocationName = dr["LocationName"].ToString();
                string Pic = dr["Pic"].ToString();
                string PicID = dr["PicID"].ToString();
                string Advantage = dr["Advantage"].ToString();
                string Tel = dr["Tel"].ToString();
                string Cell = dr["Cell"].ToString();
                string Skype = dr["Skype"].ToString();
                string Email = dr["Email"].ToString();
                string NumberCar = dr["NumberCar"].ToString();
                string Rank = dr["Rank"].ToString();
                string ServiceArea = dr["ServiceArea"].ToString();
                string Note = dr["Note"].ToString();

                string strAction = "";
                if (nPermissions >= 3)
                    strAction = "<a href=\"TruckerSale.aspx?ID=" + dr["ID"].ToString() + ((Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "") ? "&page_number=" + Request.QueryString["page_number"].ToString() : "") +
                    ((Request.QueryString["letter"] != null && Request.QueryString["letter"].ToString() != "") ? "&letter=" + Request.QueryString["letter"].ToString() : "") +
                    "\">Edit</a>";
                if (nPermissions > 7)
                {
                    strAction += " - <a href=\"javascript:doDelete('" + System.Web.HttpUtility.HtmlEncode(dr["ID"].ToString()) + "','" + System.Web.HttpUtility.HtmlEncode(BizName) + "')\">" + "Delete</a>";
                }

                string[] strValues = new string[] { ID , 
                            strIndex,
                            BizName,
                            LocationName,
                            Pic,
                            PicID,
                            Advantage,
                            Tel,
                            Cell,
                            Skype,
                            Email,
                            NumberCar,
                            Rank,
                            ServiceArea,
                            Note,
                            strAction };
                dtList.Rows.Add(strValues);
            }
        }
    }
    protected void LoadDropDownLocationTrucker()
    {
        string sqlCommand = "Select ID,Name From tbl_Location Where tbl_Location.ID in(" + Session["UserLocationIDs"].ToString() + ");" +
        "Select ID,BizName From tbl_Trucker Where tbl_Trucker.LocationID in(" + Session["UserLocationIDs"].ToString() + ");";
        DataSet ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        DataTable dt = ds.Tables[0];
        ddLocation.Items.Add((new ListItem("--Location--", "0")));
        ddLocation.AppendDataBoundItems = true;
        ddLocation.DataSource = dt;
        ddLocation.DataTextField = dt.Columns[1].ToString();
        ddLocation.DataValueField = dt.Columns[0].ToString();
        ddLocation.DataBind();
        //Location Search
        ddLocationSearch.Items.Add((new ListItem("Select Location", "All")));
        ddLocationSearch.AppendDataBoundItems = true;
        ddLocationSearch.DataSource = dt;
        ddLocationSearch.DataTextField = dt.Columns[1].ToString();
        ddLocationSearch.DataValueField = dt.Columns[0].ToString();
        ddLocationSearch.DataBind();
        //Trucker
        dt = ds.Tables[1];
        ddTrucker.Items.Add((new ListItem("--Trucker--", "0")));
        ddTrucker.AppendDataBoundItems = true;
        ddTrucker.DataSource = dt;
        ddTrucker.DataTextField = dt.Columns[1].ToString();
        ddTrucker.DataValueField = dt.Columns[0].ToString();
        ddTrucker.DataBind();
        if (Request.QueryString["ID"] != null)
        {
            TruckerSale obj = new TruckerSale();
            obj.ID = long.Parse(Request.QueryString["ID"].ToString());
            obj.Load(GV.cn, GV.trans);
            if (ddLocation.Items.FindByValue(obj.LocationID.ToString()) != null)
                ddLocation.SelectedValue = obj.LocationID.ToString();
            if (ddTrucker.Items.FindByValue(obj.TruckerID.ToString()) != null)
                ddTrucker.SelectedValue = obj.TruckerID.ToString();
        }
    }
    public string GetCustomerLink(object id, object picid)
    {
        string url = "/customer.aspx?";
        if (picid != null && id != null)
        {
            url += "picid=" + picid.ToString() + "&id=" + id.ToString();
        }
        else if (picid != null && !string.IsNullOrEmpty(picid.ToString()))
        {
            url += "picid=" + picid.ToString();
        }
        else if (picid == null && id != null)
        {
            url += "id=" + id.ToString();
        }

        return url;
    }
    protected void LoadDropDownPic()
    {
        string sql = "SELECT DISTINCT SUBSTRING_INDEX(CONCAT(UCASE(LEFT(Picnew, 1)), LCASE(SUBSTRING(Picnew, 2))), '_', 1)  AS PicSale " +
            " FROM (SELECT `PIC`, LTRIM(REPLACE((REPLACE(REPLACE(LCASE(PIC), 'ms', ''),'mr','')),'.',''))  AS picnew" +
            " FROM `tbl_TruckerSale`  Where tbl_TruckerSale.LocationID in(" + Session["UserLocationIDs"].ToString() + "))  AS tblTruckerSales " +
            " ORDER BY picnew";
        DataSet ds = DBModule.ExecuteQuery(sql, GV.cn, GV.trans);
        DataTable dtb = ds.Tables[0];
        ddPicSales.Items.Add((new ListItem("Select PIC", "All")));
        ddPicSales.AppendDataBoundItems = true;
        ddPicSales.DataSource = dtb;
        ddPicSales.DataTextField = dtb.Columns[0].ToString();
        ddPicSales.DataValueField = dtb.Columns[0].ToString();
        ddPicSales.DataBind();
    }
    protected void ddPicSales_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PicSalseTrucker"] = ddPicSales.SelectedValue;
        Response.Redirect("TruckerSale.aspx");
    }

    protected void LoadDropDownTrucker()
    {
        string sql = "Select  DISTINCT TRUCKER.BizName FROM `tbl_TruckerSale` " +
            " Left Join tbl_Trucker TRUCKER on tbl_TruckerSale.TruckerID = TRUCKER.ID " +
            " Where tbl_TruckerSale.LocationID in(" + Session["UserLocationIDs"].ToString() + ")" +
            " ORDER BY BizName";
        DataSet ds = DBModule.ExecuteQuery(sql, GV.cn, GV.trans);
        DataTable dtb = ds.Tables[0];
        ddTruckers.Items.Add((new ListItem("Select Trucker", "All")));
        ddTruckers.AppendDataBoundItems = true;
        ddTruckers.DataSource = dtb;
        ddTruckers.DataTextField = dtb.Columns[0].ToString();
        ddTruckers.DataValueField = dtb.Columns[0].ToString();
        ddTruckers.DataBind();
    }
    protected void ddTruckers_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["BizNameTrucker"] = ddTruckers.SelectedValue;
        Response.Redirect("TruckerSale.aspx");
    }
    protected void ddLocationSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["LocationTrucker"] = ddLocationSearch.SelectedValue;
        Response.Redirect("TruckerSale.aspx");
    }
}
